package db

import (
	"fmt"
	"log"
	"os"
	"path/filepath"
	"regexp"
	"strings"

	"github.com/pingcap/tidb/pkg/parser"
	"github.com/pingcap/tidb/pkg/parser/ast"
	"github.com/pingcap/tidb/pkg/parser/format"
	_ "github.com/pingcap/tidb/pkg/parser/test_driver"
)

// TableInfo 表信息
// TableInfo represents table information
type TableInfo struct {
	TableName string `db:"table_name"`
}

// GetTables 获取所有表名
// GetTables retrieves all table names from the database
func (d *DB) GetTables(includePatterns, excludePatterns []string) ([]string, error) {
	query := `SELECT table_name FROM information_schema.tables WHERE table_schema = DATABASE()`

	// 获取所有表
	var allTables []string
	err := d.Select(&allTables, query)
	if err != nil {
		return nil, fmt.Errorf("failed to get tables: %w", err)
	}

	// 首先应用包含规则
	var includedTables []string
	if len(includePatterns) > 0 {
		for _, pattern := range includePatterns {
			re, err := regexp.Compile(pattern)
			if err != nil {
				return nil, fmt.Errorf("invalid include regex pattern '%s': %w", pattern, err)
			}
			for _, table := range allTables {
				if re.MatchString(table) {
					includedTables = append(includedTables, table)
				}
			}
		}
	} else {
		// 如果没有包含规则，则所有表都进入下一轮排除
		includedTables = allTables
	}

	// 然后应用排除规则
	var finalTables []string
	if len(excludePatterns) > 0 {
		for _, pattern := range excludePatterns {
			re, err := regexp.Compile(pattern)
			if err != nil {
				return nil, fmt.Errorf("invalid exclude regex pattern '%s': %w", pattern, err)
			}
			for _, table := range includedTables {
				if !re.MatchString(table) {
					finalTables = append(finalTables, table)
				}
			}
		}
	} else {
		// 如果没有排除规则，则直接返回包含列表
		finalTables = includedTables
	}

	return finalTables, nil
}

// GetTableDDL 获取表的创建语句
// GetTableDDL retrieves the CREATE TABLE statement for a specific table
func (d *DB) GetTableDDL(tableName string) (string, error) {
	var ddl string
	query := fmt.Sprintf("SHOW CREATE TABLE `%s`", tableName)
	row := d.QueryRowx(query)

	var table string
	err := row.Scan(&table, &ddl)
	if err != nil {
		return "", fmt.Errorf("failed to get DDL for table %s: %w", tableName, err)
	}

	return ddl + ";\n", nil
}

// InitializeTableFiles creates query.sql for each table on startup.
// GenerateDDLChange compares a modified ddl.sql file with the live database schema and generates ALTER statements.
func (d *DB) GenerateDDLChange(ddlPath string) error {
	// 1. Read and parse the local ddl.sql file
	localSQLBytes, err := os.ReadFile(ddlPath)
	if err != nil {
		return fmt.Errorf("failed to read local ddl file %s: %w", ddlPath, err)
	}
	localSQL := string(localSQLBytes)
	localStmt, err := parseDDL(localSQL)
	if err != nil {
		return fmt.Errorf("failed to parse local ddl: %w", err)
	}
	tableName := localStmt.Table.Name.L

	// 2. Get the current DDL from the database
	dbDDL, err := d.GetTableDDL(tableName)
	if err != nil {
		return fmt.Errorf("failed to get database ddl for table %s: %w", tableName, err)
	}
	dbStmt, err := parseDDL(dbDDL)
	if err != nil {
		return fmt.Errorf("failed to parse database ddl: %w", err)
	}

	// 3. Compare and generate ALTER statements
	alterStmts, err := compareTables(dbStmt, localStmt)
	if err != nil {
		return fmt.Errorf("failed to compare table structures: %w", err)
	}

	if len(alterStmts) == 0 {
		log.Printf("No changes detected for table %s", tableName)
		return nil
	}

	// 4. Append the changes to ddl_change.sql
	changeSQL := "-- Changes detected for table " + tableName + "\n"
	for _, stmt := range alterStmts {
		changeSQL += stmt + ";\n"
	}
	changeSQL += "\n"

	changeFilePath := filepath.Join(filepath.Dir(ddlPath), "ddl_change.sql")
	f, err := os.OpenFile(changeFilePath, os.O_TRUNC|os.O_CREATE|os.O_WRONLY, 0644)
	if err != nil {
		return fmt.Errorf("failed to open ddl_change.sql: %w", err)
	}
	defer f.Close()

	if _, err := f.WriteString(changeSQL); err != nil {
		return fmt.Errorf("failed to write to ddl_change.sql: %w", err)
	}

	log.Printf("Successfully wrote DDL changes to %s", changeFilePath)
	return nil
}

func parseDDL(sql string) (*ast.CreateTableStmt, error) {
	// 创建TiDB parser实例
	p := parser.New()

	// 解析SQL语句
	stmts, _, err := p.Parse(sql, "", "")
	if err != nil {
		return nil, fmt.Errorf("failed to parse SQL: %w", err)
	}

	if len(stmts) == 0 {
		return nil, fmt.Errorf("no statements found in SQL")
	}

	// 检查是否是CREATE TABLE语句
	createTableStmt, ok := stmts[0].(*ast.CreateTableStmt)
	if !ok {
		return nil, fmt.Errorf("statement is not a CREATE TABLE statement")
	}

	return createTableStmt, nil
}

func extractColumns(stmt *ast.CreateTableStmt) []string {
	var columns []string

	for _, col := range stmt.Cols {
		if col.Name != nil {
			columns = append(columns, col.Name.Name.L)
		}
	}

	return columns
}

func compareTables(oldTable, newTable *ast.CreateTableStmt) ([]string, error) {
	var alterStmts []string

	if oldTable == nil || newTable == nil {
		return nil, fmt.Errorf("cannot compare nil table definitions")
	}

	tableName := newTable.Table.Name.L

	oldCols := make(map[string]*ast.ColumnDef)
	for _, col := range oldTable.Cols {
		if col.Name != nil {
			oldCols[col.Name.Name.L] = col
		}
	}

	newCols := make(map[string]*ast.ColumnDef)
	for _, col := range newTable.Cols {
		if col.Name != nil {
			newCols[col.Name.Name.L] = col
		}
	}

	// Check for added and modified columns
	for colName, newCol := range newCols {
		if oldCol, exists := oldCols[colName]; !exists {
			// Added column
			alterStmts = append(alterStmts, fmt.Sprintf("ALTER TABLE `%s` ADD COLUMN %s", tableName, getColumnDefString(newCol)))
		} else {
			// Modified column
			oldDef := getColumnDefString(oldCol)
			newDef := getColumnDefString(newCol)
			if oldDef != newDef {
				alterStmts = append(alterStmts, fmt.Sprintf("ALTER TABLE `%s` MODIFY COLUMN %s", tableName, newDef))
			}
		}
	}

	// Check for dropped columns
	for colName := range oldCols {
		if _, exists := newCols[colName]; !exists {
			alterStmts = append(alterStmts, fmt.Sprintf("ALTER TABLE `%s` DROP COLUMN `%s`", tableName, colName))
		}
	}

	// Note: This is a simplified comparison. A full implementation would also compare
	// primary keys, indexes, constraints, and table options.

	return alterStmts, nil
}

func getColumnDefString(col *ast.ColumnDef) string {
	var sb strings.Builder

	// 列名
	sb.WriteString("`")
	sb.WriteString(col.Name.Name.L)
	sb.WriteString("` ")

	// 数据类型
	if col.Tp != nil {
		sb.WriteString(col.Tp.String())
	}

	// 处理列选项
	for _, option := range col.Options {
		switch option.Tp {
		case ast.ColumnOptionNotNull:
			sb.WriteString(" NOT NULL")
		case ast.ColumnOptionNull:
			sb.WriteString(" NULL")
		case ast.ColumnOptionAutoIncrement:
			sb.WriteString(" AUTO_INCREMENT")
		case ast.ColumnOptionPrimaryKey:
			sb.WriteString(" PRIMARY KEY")
		case ast.ColumnOptionUniqKey:
			sb.WriteString(" UNIQUE")
		case ast.ColumnOptionDefaultValue:
			if option.Expr != nil {
				sb.WriteString(" DEFAULT ")
				// 使用format包来正确格式化表达式
				var buf strings.Builder
				ctx := format.NewRestoreCtx(format.DefaultRestoreFlags, &buf)
				if err := option.Expr.Restore(ctx); err == nil {
					sb.WriteString(buf.String())
				} else {
					// 如果格式化失败，使用fallback
					sb.WriteString("NULL")
				}
			}
		case ast.ColumnOptionComment:
			if option.Expr != nil {
				sb.WriteString(" COMMENT ")
				// 使用format包来正确格式化注释
				var buf strings.Builder
				ctx := format.NewRestoreCtx(format.DefaultRestoreFlags, &buf)
				if err := option.Expr.Restore(ctx); err == nil {
					sb.WriteString(buf.String())
				} else {
					// 如果格式化失败，使用fallback
					sb.WriteString("''")
				}
			}
		case ast.ColumnOptionOnUpdate:
			if option.Expr != nil {
				sb.WriteString(" ON UPDATE ")
				var buf strings.Builder
				ctx := format.NewRestoreCtx(format.DefaultRestoreFlags, &buf)
				if err := option.Expr.Restore(ctx); err == nil {
					sb.WriteString(buf.String())
				}
			}
		case ast.ColumnOptionGenerated:
			if option.Expr != nil {
				sb.WriteString(" GENERATED ALWAYS AS (")
				var buf strings.Builder
				ctx := format.NewRestoreCtx(format.DefaultRestoreFlags, &buf)
				if err := option.Expr.Restore(ctx); err == nil {
					sb.WriteString(buf.String())
				}
				sb.WriteString(")")
				if option.Stored {
					sb.WriteString(" STORED")
				} else {
					sb.WriteString(" VIRTUAL")
				}
			}
		}
	}

	return sb.String()
}

func (d *DB) InitializeTableFiles(workDir string, includeTablePatterns, excludeTablePatterns []string) error {
	tables, err := d.GetTables(includeTablePatterns, excludeTablePatterns)
	if err != nil {
		return err
	}

	tablesDir := filepath.Join(workDir, "tables")

	for _, table := range tables {
		tableDir := filepath.Join(tablesDir, table)
		if err := os.MkdirAll(tableDir, 0755); err != nil {
			return fmt.Errorf("failed to create directory for table %s: %w", table, err)
		}

		// 创建 query.sql
		querySQLPath := filepath.Join(tableDir, "query.sql")
		queryContent := fmt.Sprintf("SELECT * FROM `%s` LIMIT 100;", table)
		if err := os.WriteFile(querySQLPath, []byte(queryContent), 0644); err != nil {
			log.Printf("warning: could not write query.sql for table %s: %v", table, err)
		}
	}

	return nil
}

// GetTablePrimaryKey 获取表的主键或唯一键信息
func (d *DB) GetTablePrimaryKey(tableName string) ([]string, error) {
	query := `
		SELECT COLUMN_NAME 
		FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
		WHERE TABLE_SCHEMA = DATABASE() 
		AND TABLE_NAME = ? 
		AND CONSTRAINT_NAME = 'PRIMARY'
		ORDER BY ORDINAL_POSITION`

	var primaryKeys []string
	err := d.Select(&primaryKeys, query, tableName)
	if err != nil {
		return nil, fmt.Errorf("failed to get primary key for table %s: %w", tableName, err)
	}

	// 如果没有主键，尝试获取唯一键
	if len(primaryKeys) == 0 {
		uniqueQuery := `
			SELECT COLUMN_NAME 
			FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
			WHERE TABLE_SCHEMA = DATABASE() 
			AND TABLE_NAME = ? 
			AND CONSTRAINT_NAME IN (
				SELECT CONSTRAINT_NAME 
				FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
				WHERE TABLE_SCHEMA = DATABASE() 
				AND TABLE_NAME = ? 
				AND CONSTRAINT_TYPE = 'UNIQUE'
				LIMIT 1
			)
			ORDER BY ORDINAL_POSITION`

		err = d.Select(&primaryKeys, uniqueQuery, tableName, tableName)
		if err != nil {
			return nil, fmt.Errorf("failed to get unique key for table %s: %w", tableName, err)
		}
	}

	return primaryKeys, nil
}
